package models;

import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.content.Context;
import android.util.Log;

/**
 * This class is responsible for creating the database. 
 * The onUpdate() method will simply delete all existing data and re-create the table. 
 * It also defines several constants for the table name and the table columns.
 * @author Nikhil Karkarey, Abdelelah Salama
 *
 */

public class DbHelper extends SQLiteOpenHelper {

	public static final String TABLE_PLANNINGTASKS = "PLANNINGTASKS";
	public static final String TABLE_SCHEDULE = "SCHEDULE";
	public static final String TABLE_PREVIEW = "PREVIEW";
	public static final String TABLE_REGENERATE = "REGENERATE";
	public static final String COLUMN_ID = "_id";
	public static final String COLUMN_TITLE = "title";
	public static final String COLUMN_STARTTIME = "startTime";
	public static final String COLUMN_DURATION = "duration";
	public static final String COLUMN_TRAVELTIME = "travelTime";
	public static final String COLUMN_PRIORITY = "priority";
	public static final String COLUMN_STATUS = "status";
	
	private static final String DATABASE_NAME = "GETSTUFFDONE.Db";
	private static final int DATABASE_VERSION = 1;

	// Database creation sql statement
	private static final String PLANNINGTASKS_CREATE = "create table "
			+ TABLE_PLANNINGTASKS + "( " 
			+ COLUMN_ID + " integer primary key autoincrement, " 
			+ COLUMN_TITLE + " text not null, "
			+ COLUMN_STARTTIME + " text, "
			+ COLUMN_DURATION + " integer not null, "
			+ COLUMN_TRAVELTIME + " integer not null, "
			+ COLUMN_PRIORITY + " integer not null, "
			+ COLUMN_STATUS + " integer not null );";
	
	private static final String SCHEDULE_CREATE = "create table "
			+ TABLE_SCHEDULE + "( " 
			+ COLUMN_ID + " integer not null, " 
			+ COLUMN_TITLE + " text not null, "
			+ COLUMN_STARTTIME + " text not null, "
			+ COLUMN_DURATION + " integer not null, "
			+ COLUMN_TRAVELTIME + " integer not null, "
			+ COLUMN_PRIORITY + " integer not null, "
			+ COLUMN_STATUS + " integer not null );";
	
	private static final String PREVIEW_CREATE = "create table "
			+ TABLE_PREVIEW + "( " 
			+ COLUMN_ID + " integer not null, " 
			+ COLUMN_TITLE + " text not null, "
			+ COLUMN_STARTTIME + " text not null, "
			+ COLUMN_DURATION + " integer not null, "
			+ COLUMN_TRAVELTIME + " integer not null, "
			+ COLUMN_PRIORITY + " integer not null, "
			+ COLUMN_STATUS + " integer not null );";
	
	private static final String REGENERATE_CREATE = "create table "
			+ TABLE_REGENERATE + "( " 
			+ COLUMN_ID + " int not null, " 
			+ COLUMN_TITLE + " text not null, "
			+ COLUMN_STARTTIME + " text, "
			+ COLUMN_DURATION + " integer not null, "
			+ COLUMN_TRAVELTIME + " integer not null, "
			+ COLUMN_PRIORITY + " integer not null, "
			+ COLUMN_STATUS + " integer not null );";

	public DbHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase database) {
		database.execSQL(PLANNINGTASKS_CREATE);
		database.execSQL(SCHEDULE_CREATE);
		database.execSQL(PREVIEW_CREATE);
		database.execSQL(REGENERATE_CREATE);
		//database.execSQL("DROP TABLE IF EXISTS " + TABLE_PLANNINGTASKS);
		//database.execSQL("DROP TABLE IF EXISTS " + TABLE_SCHEDULE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(DbHelper.class.getName(),
				"Upgrading database from version " + oldVersion + " to "
						+ newVersion + ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_PLANNINGTASKS);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_SCHEDULE);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_PREVIEW);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_REGENERATE);
		onCreate(db);
	}
}